DateTime Time Zone Issues and Solutions in Entity Framework

Although many projects run solely within the Taiwan environment and do not need to consider time zone issues, the popularity of cloud environments—where many server time zones are set to Coordinated Universal Time (UTC +0)—has made this a concern that requires attention.

I have always known that the UTC format of DateTime can be a trap, so I generally try to use DateTimeOffset when dealing with time zone issues. Since I encountered a related scenario recently, I did some research and decided to document it.

A colleague reported that his project had agreed with the frontend to use UTC time, but when passing DateTime data retrieved from the database to the frontend, he found that the time was off by 8 hours. To solve this, he used the ToString() method to format the time as yyyy-MM-ddTHH:mm:ssZ.

I asked him curiously why he added Z to the end of the time string. He replied that it was the only way to prevent the time from being off by 8 hours. I looked it up, and according to the "ISO 8601" entry on Wikipedia, Z denotes the UTC +0 time zone.

I initially wanted to help him optimize this by changing how the DateTime type is handled in JsonSerializerOptions.Converters. However, I realized that many projects use DateTime for UTC +0, such as the well-known framework ABP.IO. ASP.NET Core should have accounted for this when handling formats. After checking online, it is true that DateTime in UTC format ends with Z, so I performed the following test:

DateTime localTime = new DateTime(2024, 8, 14, 8, 0, 0, DateTimeKind.Local);
DateTime utcTime = new DateTime(2024, 8, 14, 8, 0, 0, DateTimeKind.Utc);
DateTime unspecifiedTime = new DateTime(2024, 8, 14, 8, 0, 0, DateTimeKind.Unspecified);

Console.WriteLine("Local:" + localTime.ToString("O"));
Console.WriteLine("UTC:" + utcTime.ToString("O"));
Console.WriteLine("Unspecified:" + unspecifiedTime.ToString("O"));

The results are as follows:

Local:2024-08-14T08:00:00.0000000+08:00
UTC:2024-08-14T08:00:00.0000000Z
Unspecified:2024-08-14T08:00:00.0000000

Comparing this with my colleague's statement, I think the case is solved:

But when passing data retrieved from the database as DateTime to the frontend, I found the time was off by 8 hours.

The Time Zone Format Issue with DateTime

The DateTime type has a Kind property used to indicate the source of the time, with the following enumeration values:

Value	Property Name	Description
0	Unspecified	Unspecified
1	Utc	Coordinated Universal Time (UTC)
2	Local	Local time

If the Kind format is unclear, using ToLocalTime() or ToUniversalTime() to switch times will result in unexpected values.

Here is the test code:

DateTime utcNow = DateTime.UtcNow;
DateTime now = DateTime.Now;

Print("Original time:");
PrintNow("Local", now);
PrintNow("Utc", utcNow);
Console.WriteLine();

Print("Switch Kind to Local");
PrintTime(DateTime.SpecifyKind(now, DateTimeKind.Local));
Console.WriteLine();

Print("Switch Kind to Utc:");
PrintTime(DateTime.SpecifyKind(now, DateTimeKind.Utc));
Console.WriteLine();

Print("Switch Kind to Unspecified:");
PrintTime(DateTime.SpecifyKind(now, DateTimeKind.Unspecified));

void Print(string str) {
    Console.WriteLine(str);
}

void PrintNow(string title, DateTime dateTime) {
    Print($"{title}:{dateTime:O}, Kind:{dateTime.Kind}");
}

void PrintTime(DateTime dateTime) {
    Print($"Original:{dateTime:O}, Kind:{dateTime.Kind}");

    DateTime local = dateTime.ToLocalTime();
    Print($"Local:{local:O}, Kind:{local.Kind}");

    DateTime utc = dateTime.ToUniversalTime();
    Print($"Utc:{utc:O}, Kind:{utc.Kind}");
}

The results are as follows:

Original time:
Local:2024-08-15T10:35:48.8422172+08:00, Kind:Local
Utc:2024-08-15T02:35:48.8421977Z, Kind:Utc

Switch Kind to Local
Original:2024-08-15T10:35:48.8422172+08:00, Kind:Local
Local:2024-08-15T10:35:48.8422172+08:00, Kind:Local
Utc:2024-08-15T02:35:48.8422172Z, Kind:Utc

Switch Kind to Utc:
Original:2024-08-15T10:35:48.8422172Z, Kind:Utc
Local:2024-08-15T18:35:48.8422172+08:00, Kind:Local
Utc:2024-08-15T10:35:48.8422172Z, Kind:Utc

Switch Kind to Unspecified:
Original:2024-08-15T10:35:48.8422172, Kind:Unspecified
Local:2024-08-15T18:35:48.8422172+08:00, Kind:Local
Utc:2024-08-15T02:35:48.8422172Z, Kind:Utc

From the results, we can see:

• When Kind is Local, calling ToLocalTime() does not change the time.
• When Kind is Utc, calling ToUniversalTime() does not change the time.
• When Kind is Unspecified, because the type of time cannot be determined, calling ToLocalTime() causes the system to assume the original time was UTC and converts it to local time, thus adding the time zone offset. Conversely, calling ToUniversalTime() causes the system to assume the original time was local and subtracts the time zone offset.

Therefore, when ABP.IO uses DateTime, it defines an IClock interface to correct the Kind and avoid unexpected issues. Below is an excerpt of their Clock code, which determines the conversion result by comparing the configured Kind with the Kind of the time to be normalized. For more specific details, please refer to the official documentation "Timing".

public virtual DateTime Normalize(DateTime dateTime) {
    if (Kind == DateTimeKind.Unspecified || Kind == dateTime.Kind) {
        return dateTime;
    }

    if (Kind == DateTimeKind.Local && dateTime.Kind == DateTimeKind.Utc) {
        return dateTime.ToLocalTime();
    }

    if (Kind == DateTimeKind.Utc && dateTime.Kind == DateTimeKind.Local) {
        return dateTime.ToUniversalTime();
    }

    return DateTime.SpecifyKind(dateTime, Kind);
}

DateTime Time Zone Issues in Entity Framework

If database columns use types like datetime or datetime2 that do not include time zone information, the time stored in the database does not contain time zone data. However, when Entity Framework retrieves the data and maps it to the DateTime type, it cannot determine the Kind of the time, so the Kind becomes Unspecified. Consequently, the time value returned to the frontend does not include Z at the end.

In this case, the correct approach is not to append Z to the return value, but to convert the Kind of the DateTime type to Utc when retrieving data from the database. Although DateTime does not consider Kind when comparing values, calling ToLocalTime() or ToUniversalTime() when there are multiple possibilities for DateTime.Kind in the program can lead to unexpected results.

Solution

If you are using Code First, you know this is where ValueConverter comes into play. When defining the Entity structure in OnModelCreating() using Fluent API, you can use HasConversion() to handle conversions during data write and read operations. Common use cases include Enum, Enum Object, and time zone handling. For detailed information, please refer to Microsoft's documentation "Value Conversions". This article focuses on this specific problem.

You can use HasConversion() to perform the following:

• When writing data, if the DateTime Kind is not Utc, call ToUniversalTime() to convert it.
• When retrieving data, set the DateTime Kind to Utc.

The specific code is as follows:

modelBuilder.Entity<Test>(entity => {
    entity.Property(x => x.TestDateTime)
        .HasConversion(
            v => v.Kind == DateTimeKind.Utc ? v : v.ToUniversalTime(),
            v => DateTime.SpecifyKind(v, DateTimeKind.Utc)
        );
});

You can also define a UtcDateTimeValueConverter class for reuse:

public class UtcDateTimeValueConverter : ValueConverter<DateTime, DateTime> {
    public UtcDateTimeValueConverter()
        : base(v => ToDb(v), v => FromDb(v)) {
    }

    private static DateTime ToDb(DateTime dateTime) {
        return dateTime.Kind == DateTimeKind.Utc ? dateTime : dateTime.ToUniversalTime();
    }

    private static DateTime FromDb(DateTime dateTime) {
        return DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
    }
}

Use UtcDateTimeValueConverter for conversion:

modelBuilder.Entity<Test>(entity => {
    entity.Property(x => x.TestDateTime)
        .HasConversion<UtcDateTimeValueConverter>();
});

If you don't want to set it individually for every property, you can use the following approach to handle it uniformly:

foreach (IMutableEntityType entityType in modelBuilder.Model.GetEntityTypes()) {
    foreach (IMutableProperty property in entityType.GetProperties()) {
        if (property.ClrType == typeof(DateTime) || property.ClrType == typeof(DateTime?)) {
            property.SetValueConverter(typeof(UtcDateTimeValueConverter));
        }
    }
}

When using Code First, the DbContext content can be defined as you wish, so the above approach works. However, if you are using reverse engineering to generate Entities and DbContext, the DbContext usually contains the following code:

public partial class MyDbContext : DbContext {
    // Omitted...

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        // Omitted Entity definitions

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

In this case, you can write a partial class to add custom settings. Note that the namespace must match the namespace of the MyDbContext generated by reverse engineering:

public partial class MyDbContext {
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder) {
        foreach (IMutableEntityType entityType in modelBuilder.Model.GetEntityTypes()) {
            foreach (IMutableProperty property in entityType.GetProperties()) {
                if (property.ClrType == typeof(DateTime) || property.ClrType == typeof(DateTime?)) {
                    property.SetValueConverter(typeof(UtcDateTimeValueConverter));
                }
            }
        }
    }
}

Of course, I have no objection to writing a separate DbContext that inherits from the original one and using that custom DbContext in your program.

In .NET 6, there is an even simpler configuration method, ConfigureConventions(). For details, please refer to Microsoft's documentation:

public partial class MyDbContext {
    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder) {
        ArgumentNullException.ThrowIfNull(configurationBuilder);

        configurationBuilder.Properties<DateTime>().HaveConversion<UtcDateTimeValueConverter>();
    }

Since ConfigureConventions() executes before OnModelCreating(), it can be used to define default values and configuration conventions. If you want to override specific settings, it is appropriate to define them in OnModelCreatingPartial().

Change Log

• 2024-08-15 Initial document created.